USE master;
GO

SET NOCOUNT ON;
GO

DECLARE @CounterPrefix NVARCHAR(30);
SET @CounterPrefix =
	CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
	THEN 'SQLServer:'
	ELSE 'MSSQL$' + @@SERVICENAME + ':'
	END;

-- capture the first counter set
SELECT
	CAST(1 AS INT) AS collection_instance,
	[object_name],
	counter_name,
	instance_name,
	cntr_value,
	cntr_type,
	CURRENT_TIMESTAMP AS collection_time
INTO #PerformanceCounters
FROM sys.dm_os_performance_counters
WHERE ([object_name] = @CounterPrefix + 'Access Methods'
AND counter_name IN ('Full Scans/sec', 'Index Searches/sec',
	'Workfiles Created/sec', 'Worktables Created/sec',
	'Forwarded Records/sec', 'FreeSpace Scans/sec',
	'Table Lock Escalations/sec'
)
OR ([object_name] = @CounterPrefix + 'Buffer Manager'
AND counter_name IN ('Free List Stalls/sec', 'Lazy Writes/sec',
	'Page Life Expectancy', 'Page Reads/sec', 'Page Writes/sec',
	'Checkpoint Pages/sec')
)
OR ([object_name] = @CounterPrefix + 'Databases'
AND counter_name IN ('Log Bytes Flushed/sec', 'Log Flushes/sec',
	'Log Flush Wait Time')
AND instance_name = '_Total'
)
OR ([object_name] = @CounterPrefix + 'General Statistics'
AND counter_name IN ('Processes Blocked', 'User Connections')
)
OR ([object_name] = @CounterPrefix + 'Locks'
AND counter_name IN ('Lock Waits/sec', 'Lock Wait Time (ms)')
AND instance_name = '_Total'
)
OR ([object_name] = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ([object_name] = @CounterPrefix + 'SQL Statistics'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')
)
);

-- wait one second between data collection,
-- don't change the one second delay, otherwise the per second counter calculation will be wrong
WAITFOR DELAY '00:00:01';

-- capture the second counter set
INSERT INTO #PerformanceCounters
SELECT
	CAST(2 AS INT) AS collection_instance,
	[object_name],
	counter_name,
	instance_name,
	cntr_value,
	cntr_type,
	CURRENT_TIMESTAMP AS collection_time
FROM sys.dm_os_performance_counters
WHERE ([object_name] = @CounterPrefix + 'Access Methods'
AND counter_name IN ('Full Scans/sec', 'Index Searches/sec',
	'Workfiles Created/sec', 'Worktables Created/sec',
	'Forwarded Records/sec', 'FreeSpace Scans/sec',
	'Table Lock Escalations/sec'
)
OR ([object_name] = @CounterPrefix + 'Buffer Manager'
AND counter_name IN ('Free List Stalls/sec', 'Lazy Writes/sec',
	'Page Life Expectancy', 'Page Reads/sec', 'Page Writes/sec',
	'Checkpoint Pages/sec')
)
OR ([object_name] = @CounterPrefix + 'Databases'
AND counter_name IN ('Log Bytes Flushed/sec', 'Log Flushes/sec',
	'Log Flush Wait Time')
AND instance_name = '_Total'
)
OR ([object_name] = @CounterPrefix + 'General Statistics'
AND counter_name IN ('Processes Blocked', 'User Connections')
)
OR ([object_name] = @CounterPrefix + 'Locks'
AND counter_name IN ('Lock Waits/sec', 'Lock Wait Time (ms)')
AND instance_name = '_Total'
)
OR ([object_name] = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory Grants Pending'
)
OR ([object_name] = @CounterPrefix + 'SQL Statistics'
AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec')
)
);

-- calculate the cumulative counter values
SELECT
	f.[object_name],
	f.counter_name,
	f.instance_name,
	CASE WHEN f.cntr_type = 272696576  -- per second counter type, value = value_difference/time_difference
	THEN s.cntr_value - f.cntr_value  -- wait time 1 s -> value = s.cntr_value - f.cntr_value/1
	WHEN f.cntr_type = 65792 THEN s.cntr_value
	END AS counter_value
FROM #PerformanceCounters AS f INNER JOIN #PerformanceCounters AS s
ON f.collection_instance + 1 = s.collection_instance
AND f.[object_name] = s.[object_name]
AND f.counter_name = s.counter_name
AND f.instance_name = s.instance_name
ORDER BY [object_name];

-- cleanup tables
DROP TABLE #PerformanceCounters;